package com.rh.soc.zonethirdinterface.dao.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.rh.soc.bussinessdomainmanage.model.TreeVO;
import com.rh.soc.securitydomainmanage.model.SecurityDomainBriefVO;
import com.rh.soc.securitydomainmanage.model.SecurityDomainVO;
import com.rh.soc.zonethirdinterface.dao.IThirdSecurityDomainDAO;
import com.rh.webserver.common.base.dao.BaseDao;
import com.rh.webserver.common.base.exception.AppException;
import com.rh.webserver.common.util.SqlUtil;

@Repository("thirdsecuritydomaindao")
@SuppressWarnings({ "unchecked", "unused", "deprecation" })
public class ThirdSecurityDomainDAOImpl extends BaseDao implements IThirdSecurityDomainDAO {
	/**
	 * 
	 * 增加安全域信息
	 * @param vo
	 * @return
	 */
	public int addSecurityDomain(SecurityDomainVO vo) {

		StringBuffer sql = new StringBuffer(128);
		sql.append(" INSERT INTO t_domain_dict(pdoma_id,doma_id, doma_name,doma_class, ");
		sql.append("                           doma_abbreviation,doma_type,doma_level, office_tel,");
		sql.append("                          email,fax_no,doma_address,complaint_tel,doma_func,doma_memo,");
		sql.append("                          logo_path,optn_status,doma_sort,doma_status)");
		sql.append(" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(vo.getPdomaId() == null ? "-1" : vo.getPdomaId());
		paramList.add(vo.getDomaId());
		paramList.add(vo.getDomaName());
		// doma_class 安全域为1
		paramList.add(1);
		paramList.add(
				StringUtils.isNotEmpty(vo.getDomaAbbreviation().trim()) ? vo.getDomaAbbreviation() : vo.getDomaName());
		paramList.add(2);// 因为页面上没有了这个值，所以安全域设为可这个值
		paramList.add(0);// 现在没有用
		paramList.add(vo.getOfficeTel());
		paramList.add(vo.getEmail());
		paramList.add(vo.getFaxNo());
		paramList.add(vo.getDomaAddress());
		paramList.add(vo.getComplaintTel());
		paramList.add(vo.getDomaFunc());
		paramList.add(vo.getDomaMemo());
		paramList.add(vo.getLogoPath());
		// 1为有，0为没有
		paramList.add(1);
		paramList.add(vo.getDomaSort());// 权重,用于排序
		// doma_status 正常状态为1，删除为0
		paramList.add(1);

		int rtn = this.update(sql.toString(), paramList.toArray());

		addUserVSGrants(vo.getUserId(), vo.getDomaId());

		return rtn;

	}

	/**
	 * 保存用户与安全域的绑定信息
	 *
	 * @time 2011-11-10 下午02:57:21

	 */
	private void addUserVSGrants(String userIdIn, String id) {
		String sql1 = "INSERT INTO t_user_vs_grants (GRANTS_NO,PSN_ID,GRANTS_CODE) VALUES (seq_user_vs_grants.nextval,'"
				+ userIdIn + "','" + id + "')";
		this.update(sql1);
	}

	/**
	 * 查询所有树形结构的数据
	 */
	public List<SecurityDomainVO> queryAllTree() {
		StringBuffer sql = new StringBuffer();
		// sql.append(" SELECT '' treePath,level, b.* ");
		// sql.append(" FROM T_DOMAIN_DICT b ");
		// sql.append(" WHERE DOMA_STATUS = 1 ");
		// sql.append(" AND DOMA_CLASS = 0 and level > 1 ");
		// sql.append(" START WITH DOMA_ID = '-1' ");
		// sql.append(" CONNECT BY PRIOR DOMA_ID = PDOMA_ID order by level ,
		// b.pdoma_id ,b.doma_id ");
		//
		sql.append(" with domt as ");
		sql.append(" ( ");
		sql.append(" select level domlevel, LTRIM(sys_connect_by_path(a.doma_id, ','), ',') treePath,a.doma_id,");
		sql.append(" a.doma_type,a.doma_class,a.doma_status,a.doma_abbreviation,a.doma_name,a.doma_level, ");
		sql.append(" a.OFFICE_TEL,a.EMAIL,a.pdoma_id,a.doma_address,a.doma_sort ");
		sql.append(" from t_domain_dict a ");
		sql.append(" where level > 1 ");
		sql.append(" start with a.doma_id = '-1'");
		sql.append(" CONNECT BY PRIOR a.DOMA_ID = a.PDOMA_ID ");
		sql.append(" order siblings by a.doma_sort ");
		sql.append(" ), ");
		sql.append(" VAGENT AS ( ");
		sql.append(" select C.DOMA_ID, ");
		sql.append("  WM_CONCAT(d.node_NAME) node_NAME, ");
		sql.append("  WM_CONCAT(d.node_id) node_id ");
		sql.append("  from T_SOC_NODE d, T_DOMAIN_VS_AGENT C ");
		sql.append("  WHERE C.NODE_ID = D.NODE_ID ");
		sql.append("  GROUP BY C.DOMA_ID ");
		sql.append(" ) ");
		sql.append(" select domt.treePath, domt.domlevel,domt.doma_name,domt.DOMA_ABBREVIATION, ");
		sql.append(
				" domt.OFFICE_TEL,domt.EMAIL,domt.pdoma_id,domt.doma_id,VAGENT.node_NAME,domt.doma_address,domt.doma_sort ");
		sql.append(" from domt  LEFT JOIN VAGENT ON domt.doma_id=VAGENT.doma_id ");

		sql.append(" where domt.DOMA_STATUS = 1 ");
		sql.append(" AND domt.DOMA_CLASS = 1 order by domt.doma_sort ");
		// sql.append(" and b.pt_type = 'DOMA_TYPEB' ");
		SecurityDomainVO vo = new SecurityDomainVO();
		vo.setPageSize(100000);
		vo.setCurrentPageNum(1);

		return this.query(sql.toString(), vo);
	}

	/**
	 * 查询所有树形结构的数据
	 */
	public List<SecurityDomainBriefVO> queryAllTreeForBrief() {
		StringBuffer sql = new StringBuffer();
		// sql.append(" SELECT '' treePath,level, b.* ");
		// sql.append(" FROM T_DOMAIN_DICT b ");
		// sql.append(" WHERE DOMA_STATUS = 1 ");
		// sql.append(" AND DOMA_CLASS = 0 and level > 1 ");
		// sql.append(" START WITH DOMA_ID = '-1' ");
		// sql.append(" CONNECT BY PRIOR DOMA_ID = PDOMA_ID order by level ,
		// b.pdoma_id ,b.doma_id ");
		//
		sql.append(" with domt as ");
		sql.append(" ( ");
		sql.append(" select level domlevel, LTRIM(sys_connect_by_path(a.doma_id, ','), ',') treePath,a.doma_id,");
		sql.append(" a.doma_type,a.doma_class,a.doma_status,a.doma_abbreviation,a.doma_name,a.doma_level, ");
		sql.append(" a.OFFICE_TEL,a.EMAIL,a.pdoma_id,a.doma_address,a.doma_sort ");
		sql.append(" from t_domain_dict a ");
		sql.append(" where level > 1 ");
		sql.append(" start with a.doma_id = '-1'");
		sql.append(" CONNECT BY PRIOR a.DOMA_ID = a.PDOMA_ID ");
		sql.append(" order siblings by a.doma_sort ");
		sql.append(" ), ");
		sql.append(" VAGENT AS ( ");
		sql.append(" select C.DOMA_ID, ");
		sql.append("  WM_CONCAT(d.node_NAME) node_NAME, ");
		sql.append("  WM_CONCAT(d.node_id) node_id ");
		sql.append("  from T_SOC_NODE d, T_DOMAIN_VS_AGENT C ");
		sql.append("  WHERE C.NODE_ID = D.NODE_ID ");
		sql.append("  GROUP BY C.DOMA_ID ");
		sql.append(" ) ");
		sql.append(" select domt.treePath, domt.domlevel,domt.doma_name,domt.DOMA_ABBREVIATION, ");
		sql.append(
				" domt.OFFICE_TEL,domt.EMAIL,domt.pdoma_id,domt.doma_id,VAGENT.node_NAME,domt.doma_address,domt.doma_sort ");
		sql.append(" from domt  LEFT JOIN VAGENT ON domt.doma_id=VAGENT.doma_id ");

		sql.append(" where domt.DOMA_STATUS = 1 ");
		sql.append(" AND domt.DOMA_CLASS = 1 order by domt.doma_sort ");
		// sql.append(" and b.pt_type = 'DOMA_TYPEB' ");
		SecurityDomainBriefVO vo = new SecurityDomainBriefVO();
		vo.setPageSize(100000);
		vo.setCurrentPageNum(1);

		return this.query(sql.toString(), vo);
	}

	/**
	 * 根据条件查询树形数据
	 */
	public List<SecurityDomainBriefVO> queryTreeWithCondition(SecurityDomainBriefVO vo) throws AppException {
		List<SecurityDomainBriefVO> conditon = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" with domt as ");
		sql.append(" ( ");
		sql.append(" select level domlevel, LTRIM(sys_connect_by_path(a.doma_id, ','), ',') treePath,a.doma_id, ");
		sql.append(" a.doma_type,a.doma_class,a.doma_status,a.doma_abbreviation,a.doma_name,a.doma_level,");
		sql.append(" a.OFFICE_TEL,a.EMAIL,a.pdoma_id,a.doma_address,a.doma_sort ");
		sql.append(" from t_domain_dict a  ");
		sql.append(" where level > 1 ");
		sql.append(" start with a.doma_id = '-1' ");
		sql.append(" CONNECT BY PRIOR a.DOMA_ID = a.PDOMA_ID ");
		sql.append(" order siblings by a.doma_sort ");
		sql.append(" ),");
		sql.append(" VAGENT AS ( ");
		sql.append(" select C.DOMA_ID, ");
		sql.append("  WM_CONCAT(d.node_NAME) node_NAME, ");
		sql.append("  WM_CONCAT(d.node_id) node_id ");
		sql.append("  from T_SOC_NODE d, T_DOMAIN_VS_AGENT C ");
		sql.append("  WHERE C.NODE_ID = D.NODE_ID ");
		sql.append("  GROUP BY C.DOMA_ID ");
		sql.append(" ), ");
		// 添加资产数量 begin
		sql.append(" domcnt AS ");
		sql.append(" (SELECT o.pid, COUNT(1) cnt ");
		sql.append(" FROM (SELECT CONNECT_BY_ROOT(a.doma_id) pid, A.doma_id ");
		sql.append(" FROM t_domain_dict a ");
		sql.append(" START WITH exists ");
		sql.append(" (SELECT b.doma_id  FROM t_domain_dict b ");
		sql.append(" where b.doma_id = a.doma_id ");
		sql.append(" START WITH b.doma_id = '-1' ");
		sql.append(" CONNECT BY PRIOR b.doma_id = b.pdoma_id) ");
		sql.append(" CONNECT BY PRIOR a.doma_id = a.pdoma_id) o, t_ed_vs_domain y,t_entity_device e ");
		sql.append(" WHERE y.doma_id = o.doma_id  and e.ed_id = y.ed_id and e.ed_source = 1 GROUP BY o.pid) ");
		// 添加资产数量 end
		sql.append(
				" select domt.treePath, domt.domlevel,domt.doma_name || '(' || nvl(domcnt.cnt, 0) || ')' doma_name,domt.DOMA_ABBREVIATION,");
		sql.append(
				" domt.OFFICE_TEL,domt.EMAIL,domt.pdoma_id,domt.doma_id,VAGENT.node_NAME,domt.doma_address,domt.doma_sort ");
		sql.append("  from domt LEFT JOIN VAGENT ON domt.doma_id=VAGENT.doma_id ");
		sql.append(" left join domcnt on domcnt.pid = domt.doma_id ");
		sql.append("  where domt.DOMA_STATUS = 1 ");
		sql.append(" AND domt.DOMA_CLASS = 1 ");
		if (StringUtils.isNotEmpty(vo.getNodeId())) {
			/*
			 * sql.append(" where exists (select 'X' "); sql.append(" from
			 * T_DOMAIN_VS_AGENT c "); sql.append(" where domt.doma_id =
			 * c.doma_id "); sql.append(" and c.node_id in ("+vo.getNodeId()+"))
			 * "); sql.append(" and domt.DOMA_STATUS = 1 "); sql.append(" AND
			 * domt.DOMA_CLASS = 1 "); sql.append(" and b.pt_type = 'DOMA_TYPES'
			 * ");
			 */
			sql.append(" AND exists (select 'X'  from T_DOMAIN_VS_AGENT c ");
			sql.append(" where domt.doma_id = c.doma_id  and c.node_id in (" + vo.getNodeId() + ") )");
		}

		if (StringUtils.isNotEmpty(vo.getDomaName())) {
			sql.append(
					" AND domt.doma_name like ('%" + SqlUtil.convertSqlParam(vo.getDomaName()) + "%') ESCAPE '\\'  ");
		}
		if (StringUtils.isNotEmpty(vo.getDomaAbbreviation())) {
			sql.append(" and domt.doma_abbreviation like ('%" + SqlUtil.convertSqlParam(vo.getDomaAbbreviation())
					+ "%') ESCAPE '\\'  ");
		}
		/*
		 * if(StringUtils.isNotEmpty(vo.getDomaType())){ sql.append(" and
		 * domt.doma_type in ("+vo.getDomaType()+") "); }
		 */
		sql.append(" order by domt.doma_sort ");
		SecurityDomainBriefVO voo = new SecurityDomainBriefVO();
		voo.setPageSize(100000);
		voo.setCurrentPageNum(1);

		return this.query(sql.toString(), voo);

	}

	/**
	 * 查找指定节点的子节点
	 * 
	 * @return
	 */
	public List<SecurityDomainBriefVO> queryTreeChildrenList(SecurityDomainBriefVO vo) {
		List<SecurityDomainBriefVO> conditon = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" with domt as ");
		sql.append(" ( ");
		sql.append(" select level domlevel, LTRIM(sys_connect_by_path(a.doma_id, ','), ',') treePath,a.doma_id,");
		sql.append(" a.doma_type,a.doma_class,a.doma_status,a.doma_abbreviation,a.doma_name,a.doma_level, ");
		sql.append(" a.OFFICE_TEL,a.EMAIL,a.pdoma_id,a.doma_address,a.doma_sort ");
		sql.append(" from t_domain_dict a ");
		// sql.append(" where level > 1 ");
		sql.append(" start with a.doma_id = '-1'");
		sql.append(" CONNECT BY PRIOR a.DOMA_ID = a.PDOMA_ID ");
		sql.append(" order siblings by a.doma_sort ");
		sql.append(" ), ");
		sql.append(" children as ");
		sql.append("  ( select b.doma_id,count(*) childrencount ");
		sql.append("   from t_domain_dict b ");
		sql.append("  join t_domain_dict c ");
		sql.append("   on b.doma_id = c.pdoma_id ");
		sql.append("  where b.doma_class = 1 ");
		sql.append("     and b.doma_status = 1 ");
		sql.append("     and c.doma_class = 1 ");
		sql.append("     and c.doma_status = 1 ");
		sql.append("     group by  b.doma_id), ");
		sql.append(" VAGENT AS ( ");
		sql.append(" select C.DOMA_ID, ");
		sql.append("  WM_CONCAT(d.node_NAME) node_NAME, ");
		sql.append("  WM_CONCAT(d.node_id) node_id ");
		sql.append("  from T_SOC_NODE d, T_DOMAIN_VS_AGENT C ");
		sql.append("  WHERE C.NODE_ID = D.NODE_ID ");
		sql.append("  GROUP BY C.DOMA_ID ");
		sql.append(" ) ");
		sql.append(" select domt.treePath, domt.domlevel,domt.doma_name,domt.DOMA_ABBREVIATION, ");
		sql.append(
				" domt.OFFICE_TEL,domt.EMAIL,domt.pdoma_id,domt.doma_id,VAGENT.node_NAME,domt.doma_address,domt.doma_sort, ");
		sql.append(" nvl (children.childrencount,0) status   ");
		sql.append(" from domt  LEFT JOIN VAGENT ON domt.doma_id=VAGENT.doma_id ");
		sql.append("   Left JOIN children on domt.doma_id = children.doma_id ");
		sql.append(" where domt.DOMA_STATUS = 1 ");
		sql.append(" AND domt.DOMA_CLASS = 1 ");

		if (StringUtils.isNotEmpty(vo.getDomaId()) && !vo.getDomaId().equals("-1")) {
			sql.append(" and domt.pdoma_id = '" + SqlUtil.convertSqlParam(vo.getDomaId()) + "'");
		}

		sql.append(" order by domt.doma_sort ");
		SecurityDomainBriefVO voo = new SecurityDomainBriefVO();
		voo.setPageSize(100000);
		voo.setCurrentPageNum(1);

		// System.out.println(sql.toString());
		return this.query(sql.toString(), voo);
	}

	/**
	 * 
	 * 查询安全域名称是否已存在
	 * 
	 * @param domaName
	 * @param pdomaId
	 * @return

	 */
	public int queryDomaName(String domaName, String pdomaId, String domaId) {
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT doma_name, pdoma_id from t_domain_dict");
		sql.append(" WHERE doma_name = '" + domaName + "' and pdoma_id = '" + pdomaId
				+ "' and doma_status = 1 and doma_class = 1 and doma_id != '" + domaId + "'");
		SqlRowSet rowSet = this.query(sql.toString());
		if (rowSet.next()) {
			return -2;// 业务域名称存在重名
		} else {
			return 1;// 不存在重名
		}
	}

	/**
	 * 
	 * 查询安全域简称是否已存在
	 * 
	 * @param domaAbbreviation
	 * @param pdomaId
	 * @return

	 */

	public int queryDomaAbbreviation(String domaAbbreviation, String pdomaId, String domaId) {

		StringBuffer sql = new StringBuffer(128);
		sql.append(" select doma_abbreviation, pdoma_id from t_domain_dict");
		sql.append(" where doma_abbreviation = '" + domaAbbreviation + "' and pdoma_id = '" + pdomaId
				+ "' and doma_status = 1 and doma_class = 1 and doma_id != '" + domaId + "'");
		SqlRowSet rowSet = this.query(sql.toString());
		if (rowSet.next()) {
			return -3;// 业务域简称存在重名
		} else {
			return 1;// 不存在重名,即成功
		}
	}

	/**
	 * 
	 * 批量删除域信息
	 * 
	 * @param vo
	 * @return

	 */
	public int deleteSecurityDomain(SecurityDomainVO vo) {

		String id = "";
		id = vo.getDomaId().replaceAll(",", "','");
		StringBuffer sql = new StringBuffer(128);
		sql.append("update t_domain_dict");
		// 0代表该安全域被删除
		sql.append(" set doma_status = 0");
		// sql.append(" where doma_id in " + "('" + id + "')" + " or pdoma_id in
		// " + "('" + id + "')");
		sql.append("  where doma_id in " + "('" + id + "')");
		int rtn = this.update(sql.toString());
		return rtn;

	}

	/**
	 * 
	 * 删除
	 * 
	 * @param vo
	 * @return int

	 */

	public int deleteBussiness(SecurityDomainVO vo) {
		StringBuffer sql = new StringBuffer(128);
		sql.append("update t_domain_dict");
		sql.append(" set doma_status = 0");// 0代表该安全域被删除
		sql.append("  where doma_id = (?) or pdoma_id = (?)");
		Object para[] = { vo.getDomaId(), vo.getDomaId() };
		int rtn = this.update(sql.toString(), para);
		return rtn;
	}

	/**
	 * 
	 * {删除用户安全域的数据权限}
	 * 
	 * @param vo
	 * @return

	 */
	public int delUserVsGrants(SecurityDomainVO vo) {
		String id = "";
		id = vo.getDomaId().replaceAll(",", "','");
		StringBuffer sql = new StringBuffer(128);
		sql.append(" delete from t_user_vs_grants t where t.grants_code in ('" + id + "')");
		int rtn = this.update(sql.toString());
		return rtn;
	}

	/**
	 * 
	 * 查询安全域全部信息
	 * 
	 * @param vo
	 * @return

	 */

	@SuppressWarnings("unchecked")
	public List<SecurityDomainVO> queryAllSecurityDomain(SecurityDomainVO vo) {
		StringBuffer sql = new StringBuffer(128);
		String domaName = vo.getDomaName();
		String domaAbbreviation = vo.getDomaAbbreviation();
		String domaType = vo.getDomaType();
		String pdomaId = vo.getPdomaId();
		String domaId = vo.getDomaId();
		String domaAgent = vo.getNodeId() == null ? "" : vo.getNodeId();
		List<Object> condition = new ArrayList<Object>();
		List result = null;

		sql.append("select t3.pdoma_id, ");
		sql.append("t3.doma_id, ");
		sql.append("t3.doma_name, ");
		sql.append("t3.doma_status, ");
		sql.append("t3.doma_class, ");
		sql.append("t3.doma_abbreviation, ");
		sql.append("t3.doma_type, ");
		sql.append("t3.doma_level, ");
		sql.append("t3.office_tel, ");
		sql.append("t3.email, ");
		sql.append("t3.fax_no, ");
		sql.append("t3.doma_address, ");
		sql.append("t3.complaint_tel, ");
		sql.append("t3.doma_func, ");
		sql.append("t3.doma_memo, ");
		sql.append("t3.logo_path, ");
		sql.append("t3.optn_status, ");
		sql.append("t3.doma_sort, ");
		sql.append("t3.node_id FROM (");

		sql.append("select t1.pdoma_id, ");
		sql.append("t1.doma_id, ");
		sql.append("t1.doma_name, ");
		sql.append("t1.doma_status, ");
		sql.append("t1.doma_class, ");
		sql.append("t1.doma_abbreviation, ");
		sql.append("t1.doma_type, ");
		sql.append("t1.doma_level, ");
		sql.append("t1.office_tel, ");
		sql.append("t1.email, ");
		sql.append("t1.fax_no, ");
		sql.append("t1.doma_address, ");
		sql.append("t1.complaint_tel, ");
		sql.append("t1.doma_func, ");
		sql.append("t1.doma_memo, ");
		sql.append("t1.logo_path, ");
		sql.append("t1.optn_status, ");
		sql.append("t1.doma_sort, ");
		sql.append(" WM_CONCAT(t2.node_id) node_id ");
		sql.append(" from t_domain_dict t1 ");
		sql.append(" left join T_DOMAIN_VS_AGENT t2 on t2.doma_id = t1.doma_id ");
		sql.append(" WHERE ");
		sql.append(" t1.doma_status = 1 ");
		sql.append(" AND t1.doma_class = 1 ");

		if (StringUtils.isNotEmpty(domaName)) {
			sql.append(" AND t1.doma_name like ? ESCAPE '\\' ");
			condition.add("%" + domaName + "%");
		}
		if (StringUtils.isNotEmpty(domaAbbreviation)) {
			sql.append(" AND t1.doma_abbreviation like ? ESCAPE '\\' ");
			condition.add("%" + domaAbbreviation + "%");
		}
		if (StringUtils.isNotEmpty(domaType)) {
			sql.append(" AND t1.doma_type in (" + convertToSql(domaType) + " )");

		}
		if (StringUtils.isNotEmpty(pdomaId)) {
			sql.append(" AND t1.pdoma_id = ? ESCAPE '\\' ");
			condition.add(pdomaId);
		}
		if (StringUtils.isNotEmpty(domaId)) {
			sql.append(" AND t1.doma_id = ? ESCAPE '\\' ");
			condition.add(domaId);
		}

		sql.append(" GROUP BY t1.pdoma_id,t1.doma_id, t1.doma_name,t1.doma_status, ");
		sql.append("          t1.doma_class,t1.doma_abbreviation,t1.doma_type, ");
		sql.append("          t1.doma_level, t1.office_tel,t1.email,t1.fax_no,");
		sql.append("          t1.doma_address,t1.complaint_tel,t1.doma_func,");
		sql.append("          t1.doma_memo,t1.logo_path,t1.optn_status,t1.doma_sort ");
		sql.append(" ORDER BY t1.doma_id desc ) t3");
		sql.append(" WHERE 1=1 ");

		if (StringUtils.isNotEmpty(domaAgent)) {
			String[] ids = domaAgent.split(",");
			sql.append(" AND t3.node_id LIKE ?  ESCAPE '\\' ");
			condition.add(SqlUtil.convertSqlParam(ids[0]));
			for (int i = 1; i < ids.length; i++) {
				sql.append(" OR t3.node_id LIKE ? ESCAPE '\\' ");
				condition.add(SqlUtil.convertSqlParam(ids[i]));
			}
		}

		if (condition.size() > 0) {
			result = this.query(sql.toString(), vo, condition.toArray());
		} else {

			result = this.query(sql.toString(), vo);
		}
		return result;
	}

	/**
	 * 
	 * 把以逗号分隔的字符串，转换为sql里以逗号分隔的字符串
	 * 
	 * @param idStr
	 * @return

	 */

	public static String convertToSql(String idStr) {
		StringBuilder str = new StringBuilder();
		String[] ids = idStr.split(",");
		for (String id : ids) {
			str.append("'" + id + "'");
			str.append(",");
		}
		str.deleteCharAt(str.length() - 1);
		return str.toString();
	}

	/**
	 * 
	 * 查询代理引擎NodeId
	 * 
	 * @param domaId
	 * @return

	 */
	public String queryNodeId(String domaId) {
		String nodeId = "";
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT t.node_id, j.node_name ");
		sql.append(" FROM   t_domain_vs_agent t left join t_soc_node j on t.node_id = j.node_id ");
		sql.append(" WHERE t.doma_id = '" + domaId + "' ");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			nodeId = nodeId + rowSet.getString("node_id") + ",";
		}

		nodeId = nodeId.substring(0, (nodeId.length() - 1));
		return nodeId;

	}

	/**
	 * 
	 * 查询代理引擎NodeName
	 * 
	 * @param domaId
	 * @return

	 */
	public String queryNodeName(String domaId) {
		String nodeName = "";
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT t.node_id, j.node_name ");
		sql.append(" FROM   t_domain_vs_agent t left join t_soc_node j on t.node_id = j.node_id ");
		sql.append(" WHERE t.doma_id = '" + domaId + "' ");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			nodeName = nodeName + rowSet.getString("node_name") + ",";
		}

		nodeName = nodeName.substring(0, (nodeName.length() - 1));
		return nodeName;

	}

	/**
	 * 
	 * 查询隶属安全域名称PdomaName
	 * 
	 * @param pdomaId
	 * @return

	 */

	public String queryPdomaName(String pdomaId) {
		String pdomaName = "";
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT doma_name ");
		sql.append(" FROM   t_domain_dict ");
		sql.append(" WHERE  doma_id = '" + pdomaId + "' and doma_status = 1");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			pdomaName = rowSet.getString("doma_name");
		}

		return pdomaName;

	}

	/**
	 * 
	 * 修改安全域信息
	 * 
	 * @param vo
	 * @return

	 */
	public int updateSecurityDomain(SecurityDomainVO vo) {
		StringBuffer sql = new StringBuffer(128);
		sql.append(" UPDATE t_domain_dict");
		sql.append(" SET pdoma_id = ?,doma_name = ?,doma_class  = ?,doma_abbreviation = ?,");
		sql.append("     doma_level = ?,office_tel = ?,email = ?,fax_no = ?,doma_address = ?,");
		sql.append("     complaint_tel = ?,doma_func = ?,doma_memo = ?,logo_path = ?,optn_status = ?,");
		sql.append("     doma_sort = ?,doma_status = ?");
		sql.append(" WHERE doma_id = ?");
		Object[] param = new Object[] { vo.getPdomaId() == null ? "-1" : vo.getPdomaId(), vo.getDomaName(), 1,
				StringUtils.isNotEmpty(vo.getDomaAbbreviation().trim()) ? vo.getDomaAbbreviation() : vo.getDomaName(),
				0, vo.getOfficeTel(), vo.getEmail(), vo.getFaxNo(), vo.getDomaAddress(), vo.getComplaintTel(),
				vo.getDomaFunc(), vo.getDomaMemo(), vo.getLogoPath(), 1, vo.getDomaSort(), 1, vo.getDomaId() };
		return this.update(sql.toString(), param);

	}

	/**
	 * 
	 * 修改t_staff_structure
	 * 
	 * @param ssName
	 * @param domaId
	 * @return

	 */

	public int updateStaffStructure(String ssName, String domaId) {
		StringBuffer sql = new StringBuffer(128);
		sql.append(" UPDATE t_staff_structure");
		sql.append(" SET    ss_name = ? where doma_id = ?");
		Object para[] = { ssName, domaId };
		int rtn = this.update(sql.toString(), para);
		return rtn;

	}

	/**
	 * 
	 * 根据系统名称和local状态在t_local_dict中查出local_code
	 * 
	 * @param localStatus
	 * @param localName
	 * @return

	 */

	public String queryLocal(String localStatus, String localName) {

		String sql = "SELECT t.local_name FROM t_local_dict t WHERE t.local_status=0 and t.local_code='1'";
		SqlRowSet rowSet = this.query(sql);
		String result = null;
		while (rowSet.next()) {
			result = rowSet.getString("local_name");
		}
		return result;
	}

	/**
	 * 
	 * 根据安全域id 和安全域名称在t_staff_structure中插入一条记录
	 * 
	 * @param domaId
	 * @param domaName
	 * @return

	 */

	public String addStaffStructure(String domaId, String domaName) {

		StringBuffer sql = new StringBuffer(128);
		sql = sql.append("INSERT INTO t_staff_structure(ss_id,ss_name,doma_id,ss_sort,remark)" + " VALUES(?,?,?,?,?)");
		String id = this.nextValue("SEQ_STAFF_STRUCTURE");
		Object[] para = { id, domaName, domaId, 0, " " };
		this.update(sql.toString(), para);
		return id;

	}

	/**
	 * 
	 * 根据安全域id 和页面上选择的域代理向t_domain_vs_agent中插入一条记录
	 * 
	 * @param domaId
	 * @param nodeId
	 * @return

	 */
	public int addDomainVsAgent(String domaId, String nodeId) {

		final List<SecurityDomainVO> list = new ArrayList<SecurityDomainVO>();
		if (StringUtils.isNotEmpty(nodeId)) {
			String[] nodeIds = nodeId.split(",");
			for (String id : nodeIds) {
				SecurityDomainVO vo = new SecurityDomainVO();
				vo.setDomaId(domaId);
				vo.setNodeId(id);
				list.add(vo);
			}
		}
		if (list.size() > 0) {
			String sql = "INSERT INTO t_domain_vs_agent(doma_id,node_id) VALUES(?,?)";
			this.batchUpdate(sql, new BatchPreparedStatementSetter() {
				public int getBatchSize() {
					return list.size();
				}

				public void setValues(PreparedStatement ps, int i) throws SQLException {
					SecurityDomainVO vo = list.get(i);
					ps.setString(1, vo.getDomaId());
					ps.setString(2, vo.getNodeId());
				}
			});
		}
		return 1;
	}

	/**
	 * 
	 * 删除t_domain_vs_agent中的记录
	 * 
	 * @param domaId

	 */
	public void delDomainVsAgent(String domaId) {

		StringBuffer sql = new StringBuffer(128);
		sql.append("delete from t_domain_vs_agent where doma_id = ?");
		Object para[] = { domaId };
		this.update(sql.toString(), para);
	}

	/**
	 * 
	 * 批量删除t_domain_vs_agent中的记录
	 * 
	 * @param vo

	 */
	public int delDomainVsAgentBatch(SecurityDomainVO vo) {

		// StringBuffer sql = new StringBuffer(128);
		// sql.append("delete from t_domain_vs_agent where doma_id = ?");
		// Object para[] = { domaId };
		// this.update(sql.toString(), para);

		String id = "";
		id = vo.getDomaId().replaceAll(",", "','");
		StringBuffer sql = new StringBuffer(128);
		sql.append("delete from t_domain_vs_agent where doma_id in ('" + id + "')");
		int rtn = this.update(sql.toString());
		return rtn;
	}

	/**
	 * 
	 * 查询详细信息
	 * 
	 * @param domaId
	 * @return

	 */

	public SecurityDomainVO querySecurityDetail(String domaId) {
		SecurityDomainVO vo = new SecurityDomainVO();
		vo.setPageSize(10);
		vo.setCurrentPageNum(1);
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT pdoma_id,doma_id,doma_name,doma_status, ");
		sql.append("        doma_class,doma_abbreviation,doma_type,doma_level,");
		sql.append("        office_tel,email,fax_no,doma_address,complaint_tel,");
		sql.append(
				"        doma_func,doma_memo,logo_path,optn_status,doma_sort,t.pt_name, t.pt_id,z.latitude gisY,z.longitude gisX");
		sql.append(" FROM   t_domain_dict x left join t_public_types t on doma_type = t.pt_id ");
		// yubaoqi add 查询gis的坐标
		sql.append("left join t_gis_llude z on x.doma_id = z.object_id and z.object_type='1' ");
		// add end
		sql.append(" WHERE  doma_status = 1 ");
		sql.append(" AND doma_id = ?");
		Object[] para = { domaId };
		List<SecurityDomainVO> list = this.query(sql.toString(), vo, para);
		if (list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 
	 * 查询该域内是否有人员，若有查询对应人员所具有的所有的Id号(出去roleId)
	 * 
	 * @param secureDomainvo
	 * @return

	 */
	public SecurityDomainVO queryALLInfoId(SecurityDomainVO secureDomainvo) {
		String domaId = secureDomainvo.getDomaId();
		SecurityDomainVO vo = new SecurityDomainVO();
		vo.setPageSize(10);
		vo.setCurrentPageNum(1);
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT a.doma_id,b.ss_id,c.staff_id,d.user_id ");
		sql.append(" FROM   t_domain_dict a left join t_staff_structure b on a.doma_id = b.doma_id ");
		sql.append("        left join t_staff_domain c on b.ss_id = c.ss_id  ");
		sql.append("        left join t_org_user d on c.staff_id = d.staff_id ");
		sql.append(" WHERE  a.doma_id = ? ");
		Object[] para = { domaId };

		List<SecurityDomainVO> list = this.query(sql.toString(), vo, para);
		if (list.size() > 0) {
			return list.get(0);
		}
		return vo;
	}

	/**
	 * 
	 * 查询该人员是否具有角色Id号
	 * 
	 * @param secureDomainvo
	 * @return

	 */
	public String queryRoleIsExists(SecurityDomainVO secureDomainvo) {
		String userId = secureDomainvo.getUserId();
		String result = "";
		String sql = " SELECT  distinct user_id from t_org_user_busirole  where user_id = ? ";
		SqlRowSet rs = this.query(sql, userId);

		if (rs.next()) {
			result = rs.getString(1);
		}
		return result;

	}

	/**
	 * 
	 * 查询该域内是否有人员，若有查询对应人员所具有的所有的Id号(除去roleId)
	 * 
	 * @param secureDomainvo
	 * @return

	 */
	public List<SecurityDomainVO> queryStaffAndUser(SecurityDomainVO secureDomainvo) {
		String domaId = secureDomainvo.getDomaId();
		SecurityDomainVO vo = new SecurityDomainVO();
		vo.setPageSize(10);
		vo.setCurrentPageNum(1);
		StringBuffer sql = new StringBuffer(128);
		sql.append(" SELECT a.doma_id,b.ss_id,c.staff_id,d.user_id ");
		sql.append(" FROM   t_domain_dict a left join t_staff_structure b on a.doma_id = b.doma_id  ");
		sql.append("        left join t_staff_domain c on b.ss_id = c.ss_id ");
		sql.append("        left join t_org_user d on c.staff_id = d.staff_id ");
		sql.append(" WHERE a.doma_id = ? ");
		Object[] para = { domaId };
		List<SecurityDomainVO> list = new ArrayList<SecurityDomainVO>();// new
		list = this.query(sql.toString(), vo, para);
		return list;

	}

	/**
	 * 
	 * 检查该域是否存在
	 * 
	 * @param domaId
	 * @return

	 */
	public int checkSecurityIsExists(String domaId) {
		String sql = "SELECT doma_id FROM t_domain_dict WHERE doma_id = ? ";
		SqlRowSet rs = this.query(sql, domaId);
		int result = 0;
		if (rs.next()) {
			rs.getString(1);
			result = 1;

		}
		return result;

	}

	/**
	 * 
	 * 判断同一代理引擎下的网段是否重复
	 * 
	 * @param vo
	 * @return

	 */
	public List<SecurityDomainVO> queryIsExistIp(SecurityDomainVO vo) {
		String nodeId = vo.getNewNodeId().replaceAll(",", "','");
		StringBuilder sql = new StringBuilder(128);
		sql.append(" SELECT c.ip_name doma_ip_name, c.ipv_start doma_ip_start, ");
		sql.append(" 		c.ipv_end doma_ip_end, b.node_id agent_id,");
		sql.append(" 		t1.node_name agent_name,a.ip_name agent_ip_name, ");
		sql.append("        a.ipv_start agent_ip_start, a.ipv_end agent_ip_end,");
		sql.append(" 		a.doma_id ,t2.doma_name ");
		sql.append(" FROM   t_domain_ip_mgr a, t_domain_vs_agent b, t_domain_ip_mgr c, T_SOC_NODE t1,T_DOMAIN_DICT t2");
		sql.append(" WHERE  a.doma_id = b.doma_id");
		sql.append(" and (decode(c.ip_start,9999999991,c.ipv_start,c.ip_start)  ");
		sql.append(" between decode(a.ip_start,9999999991,a.ipv_start,a.ip_start)");
		sql.append(" and decode(a.ip_end,9999999991,a.ipv_end,a.ip_end)");
		sql.append(" or decode(c.ip_end,9999999991,c.ipv_end,c.ip_end) ");
		sql.append(" between decode(a.ip_start,9999999991,a.ipv_start,a.ip_start) ");
		sql.append(" and decode(a.ip_end,9999999991,a.ipv_end,a.ip_end))");
		sql.append(" and b.node_id in ('" + nodeId + "')");
		sql.append(" and c.doma_id = '" + vo.getDomaId() + "'");
		sql.append(" and b.node_id = t1.node_id");
		sql.append(" and a.doma_id = t2.doma_id");

		return this.query(sql.toString(), vo);

	}

	/**
	 * 
	 * 判断该域下是否存在子域
	 * 
	 * @param vo
	 * @return

	 */
	public List<SecurityDomainVO> isExistsChildDomain(SecurityDomainVO vo) {
		String domaId = vo.getDomaId();
		SecurityDomainVO sdvo = new SecurityDomainVO();
		sdvo.setPageSize(10);
		sdvo.setCurrentPageNum(1);
		StringBuilder sql = new StringBuilder(128);
		sql.append(" SELECT t2.doma_id");
		sql.append(" FROM t_domain_dict t1, t_domain_dict t2 ");
		sql.append("WHERE t1.doma_id = t2.pdoma_id AND t1.doma_id = ?");
		return this.query(sql.toString(), sdvo, domaId);
	}

	/**
	 * 
	 * 查询所有根节点下的域
	 * 
	 * @param vo
	 * @return

	 */
	public List<SecurityDomainVO> queryRootDomain(SecurityDomainVO vo) {

		StringBuffer sql = new StringBuffer();
		vo.setPageSize(100000);
		vo.setCurrentPageNum(1);
		List<Object> condition = new ArrayList<Object>();
		List result = null;
		sql.append(" select t.pdoma_id, ");
		sql.append("    t.doma_id, ");
		sql.append("    t.doma_name, ");
		sql.append("    t.doma_class, ");
		sql.append("    t.doma_abbreviation, ");
		sql.append("    t.doma_type, ");
		sql.append("    t.doma_level, ");
		sql.append("    t.office_tel, ");
		sql.append("    t.email, ");
		sql.append("    t.fax_no, ");
		sql.append("    t.doma_address, ");
		sql.append("    t.complaint_tel, ");
		sql.append("    t.doma_func, ");
		sql.append("    t.doma_memo, ");
		sql.append("    t.logo_path, ");
		sql.append("    t.optn_status,t.doma_sort,t.doma_status,t1.pt_name ");
		sql.append(" from t_domain_dict t ");
		sql.append("  left join t_public_types t1 on  pt_id=t.doma_type ");

		// sql.append(" where t.doma_status = '1' ");//
		// sql.append(" and t.doma_class = '0' ");
		// sql.append(" and t.pdoma_id = '-1' ");//

		if (StringUtils.isNotEmpty(vo.getNodeId())) {
			sql.append(" left join T_DOMAIN_VS_AGENT t2 on t2.doma_id = t.doma_id ");
			// sql.append(" and t2.node_id in(" + convertToSql(vo.getNodeId()) +
			// ") ");//
		}
		sql.append(" where t.doma_status = '1' ");//
		sql.append(" and t.doma_class = '1' ");
		sql.append(" and t.pdoma_id = '-1' ");//
		if (StringUtils.isNotEmpty(vo.getNodeId())) {
			// sql.append(" left join T_DOMAIN_VS_AGENT t2 on t2.doma_id =
			// t.doma_id ");
			sql.append(" and t2.node_id in(" + convertToSql(vo.getNodeId()) + ") ");//
		}
		if (StringUtils.isNotEmpty(vo.getDomaName())) {
			sql.append(" AND t.doma_name like ? ESCAPE '\\' ");
			condition.add("%" + vo.getDomaName() + "%");
		}
		if (StringUtils.isNotEmpty(vo.getDomaAbbreviation())) {
			sql.append(" AND t.doma_abbreviation like ? ESCAPE '\\' ");
			condition.add("%" + vo.getDomaAbbreviation() + "%");
		}
		if (StringUtils.isNotEmpty(vo.getDomaType())) {
			sql.append(" AND t.doma_type in (" + convertToSql(vo.getDomaType()) + " )");
			// condition.add(vo.getDomaType());

		}

		if (condition.size() > 0) {
			result = this.query(sql.append(" order by t.doma_name").toString(), vo, condition.toArray());
		} else {

			result = this.query(sql.append(" order by t.doma_name").toString(), vo);
		}

		// return this.query(sql.toString(), vo);//
		return result;

	}

	/**
	 * 
	 * 查询某个域下的子域
	 * 
	 * @param voobj
	 * @return

	 */
	public List<SecurityDomainVO> querySubDomain(SecurityDomainVO voobj) {
		StringBuffer sql = new StringBuffer();
		voobj.setPageSize(100000);
		voobj.setCurrentPageNum(1);
		List<Object> condition = new ArrayList<Object>();
		List result = null;
		sql.append(" select t.pdoma_id, ");
		sql.append("    t.doma_id, ");
		sql.append("    t.doma_name, ");
		sql.append("    t.doma_class, ");
		sql.append("    t.doma_abbreviation, ");
		sql.append("    t.doma_type, ");
		sql.append("    t.doma_level, ");
		sql.append("    t.office_tel, ");
		sql.append("    t.email, ");
		sql.append("    t.fax_no, ");
		sql.append("    t.doma_address, ");
		sql.append("    t.complaint_tel, ");
		sql.append("    t.doma_func, ");
		sql.append("    t.doma_memo, ");
		sql.append("    t.logo_path, ");
		sql.append("    t.optn_status,t.doma_sort,t.doma_status,t1.pt_name  ");
		sql.append(" from T_DOMAIN_DICT t ");
		sql.append("  left join t_public_types t1 on  pt_id=t.doma_type ");
		if (StringUtils.isNotEmpty(voobj.getNodeId())) {
			sql.append(" left join T_DOMAIN_VS_AGENT t2 on t2.doma_id = t.doma_id ");
			// sql.append(" and t2.node_id in(" + convertToSql(vo.getNodeId()) +
			// ") ");//
		}
		sql.append(" where t.doma_status = '1' ");
		sql.append(" and t.pdoma_id = ? ");
		condition.add(voobj.getPdomaId());
		if (StringUtils.isNotEmpty(voobj.getNodeId())) {
			sql.append(" and t2.node_id in(" + convertToSql(voobj.getNodeId()) + ") ");//
			// condition.add("%" + voobj.getNodeId() + "%");
		}
		if (StringUtils.isNotEmpty(voobj.getDomaName())) {
			sql.append(" AND t.doma_name like ? ESCAPE '\\' ");
			condition.add("%" + voobj.getDomaName() + "%");
		}
		if (StringUtils.isNotEmpty(voobj.getDomaAbbreviation())) {
			sql.append(" AND t.doma_abbreviation like ? ESCAPE '\\' ");
			condition.add("%" + voobj.getDomaAbbreviation() + "%");
		}
		if (StringUtils.isNotEmpty(voobj.getDomaType())) {
			sql.append(" AND t.doma_type in (" + convertToSql(voobj.getDomaType()) + " )");
			// condition.add("%" + voobj.getDomaType() + "%");

		}
		
		sql.append(" order by t.doma_sort asc");
		
		if (condition.size() > 0) {
			result = this.query(sql.toString(), voobj, condition.toArray());
		} else {

			result = this.query(sql.toString(), voobj);
		}

		// BussinessDomainVO voobj = new BussinessDomainVO();//

		// return this.query(sql.toString(), voobj, new
		// Object[]{vo.getDomaId()});//
		return result;
	}

	/**
	 * 
	 * 查询子域下的Id信息（Sql递归查询）
	 * 
	 * @param vo
	 * @return

	 */
	public String queryChildSecDomain(SecurityDomainVO vo) {
		String delIds = "";
		String id = vo.getDelIds().replaceAll(",", "','");
		StringBuilder sql = new StringBuilder(128);
		sql.append("select distinct  doma_id  from t_domain_dict where doma_status = 1 and doma_class = 1");
		sql.append("start with doma_id in ('" + id + "') ");
		sql.append("connect by prior   doma_id =  pdoma_id");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			delIds = delIds + rowSet.getString("doma_id") + ",";
		}
		if (StringUtils.isNotEmpty(delIds)) {
			delIds = delIds.substring(0, (delIds.length() - 1));
		}

		return delIds;
	}

	/**
	 * 
	 * 查询该域下是否有资产
	 * 
	 * @param domaId
	 * @return

	 */
	public String isHasDevice(String domaId) {
		String resultStr = "";
		String[] arr = domaId.split(",");
		String domaIds = "";
		for (int i = 0; i < arr.length; i++) {
			domaIds = domaIds + "'" + arr[i] + "'" + ",";
		}
		domaIds = domaIds.substring(0, domaIds.length() - 1);
		StringBuilder sql = new StringBuilder(128);
		sql.append(" select count(1) num ");
		sql.append(" from T_ENTITY_DEVICE t1  ");
		sql.append(" JOIN   T_ED_VS_DOMAIN t2 on t2.ed_id = t1.ed_id ");
		sql.append(" where 1=1 ");
		sql.append(" and t1.ed_status = 0");
		sql.append(" and t2.doma_id in (" + domaIds + ") and t1.ed_source = 1 ");
		/*
		 * sql.append(" select count(1) num "); sql.append(" from T_ED_VS_DOMAIN
		 * t1 "); sql.append(" where 1=1 "); sql.append(" and t1.doma_id in ("
		 * +domaIds + ")");;
		 */
		SqlRowSet rs = super.query(sql.toString());
		if (rs.next()) {
			resultStr = rs.getString("num");
		}

		if ("0".equals(resultStr)) {
			StringBuilder sql1 = new StringBuilder(128);
			sql1.append(" select count(1) num ");
			sql1.append(" from T_ENTITY_DEVICE t1  ");
			sql1.append(" JOIN   T_ED_VS_DOMAIN t2 on t2.ed_id = t1.ed_id ");
			sql1.append(" where 1=1 ");
			sql1.append(" and t1.ed_status = 0");
			sql1.append(" and t2.doma_id in (" + domaIds + ") and t1.ed_source = 2 ");
			SqlRowSet rs1 = super.query(sql1.toString());
			long num1 = 0;
			if (rs1.next()) {
				num1 = rs1.getLong("num");
			}
			if (num1 > 0) {
				resultStr = "-1";
			}
		}

		return resultStr;
	}

	/**
	 * 
	 * 查询子域下的Id信息
	 * 
	 * @param vo
	 * @return

	 */
	public String queryChildDomain(SecurityDomainVO vo) {
		String delIds = "";
		String id = vo.getDelIds().replaceAll(",", "','");
		StringBuilder sql = new StringBuilder(128);
		sql.append("select distinct  doma_id  from t_domain_dict where doma_status = 1 and doma_class = 1");
		sql.append("start with doma_id in ('" + id + "') ");
		sql.append("connect by prior   doma_id =  pdoma_id");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			delIds = delIds + rowSet.getString("doma_id") + ",";
		}
		if (StringUtils.isNotEmpty(delIds)) {
			delIds = delIds.substring(0, (delIds.length() - 1));
		}

		return delIds;
	}

	/**
	 * 
	 * 添加域时需要向有关扫描区域的两张表添加数据
	 * 
	 * @param vo
	 * @return

	 */
	public int addScanZone(SecurityDomainVO vo) {
		String Sql1 = "insert into t_agent_scanzone  values(?,?)";
		String Sql2 = " insert into t_ascanzone_vs_ru t values(?,?)";
		this.update(Sql1, vo.getDomaId(), vo.getDomaName());
		this.update(Sql2, vo.getDomaId(), vo.getDomaId());

		return 1;

	}

	/**
	 * 
	 * 删除域时同时删除扫描区域表中的内容
	 * 
	 * @param vo
	 * @return

	 */
	public int delScanZone(SecurityDomainVO vo) {
		String id = "";
		/*
		 * if("".equals(vo.getDelIds())){ id = vo.getDomaId(); }else{ id =
		 * vo.getDelIds().replaceAll(",", "','"); }
		 */
		id = vo.getDomaId().replaceAll(",", "','");
		String del1 = "delete from t_ascanzone_vs_ru where scanzone_id  in " + "('" + id + "')";
		String del2 = " delete from t_agent_scanzone where scanzone_id in " + "('" + id + "')";
		this.update(del1);
		this.update(del2);
		return 1;

	}

	/**
	 * 
	 * 修改安全域时修改扫描区域表信息
	 * 
	 * @param domaName
	 * @param domaId
	 * @return

	 */
	public int updScanZone(String domaName, String domaId) {
		StringBuffer sql = new StringBuffer(128);
		sql.append(" UPDATE t_agent_scanzone");
		sql.append(" SET    scanzone_name = ? where scanzone_id = ?");
		Object para[] = { domaName, domaId };
		int rtn = this.update(sql.toString(), para);
		return rtn;

	}

	/**
	 * 
	 * 获取安全域树信息
	 * 
	 * @param vo
	 * @return

	 */
	public List<TreeVO> querySecurityDomainTree(TreeVO vo) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT DECODE(T.PDOMA_ID, '-1', 'roota', T.PDOMA_ID) PID, ");
		sql.append(" DOMA_ID ID, ");
		sql.append(" DOMA_NAME LABEL, ");
		sql.append(" DECODE(NVL(T3.CHILDNUM, 0), 0, 'true', 'false') LEAF ");
		sql.append(" FROM T_DOMAIN_DICT T ");
		sql.append(" LEFT JOIN (SELECT NVL(COUNT(*), 0) CHILDNUM, T2.PDOMA_ID ");
		sql.append(" FROM T_DOMAIN_DICT T2 ");
		sql.append(" WHERE T2.DOMA_CLASS = 1 ");
		sql.append(" AND T2.DOMA_STATUS = 1 ");
		sql.append(" GROUP BY T2.PDOMA_ID) T3 ON T.DOMA_ID = T3.PDOMA_ID ");
		sql.append(" WHERE (T.DOMA_STATUS = 1 AND T.DOMA_CLASS = 1 AND T.DOMA_ID != 'BDUNKNOWN' ");
		if (StringUtils.isNotEmpty(vo.getDomaId())) {

			sql.append(" and doma_id  not in ");
			sql.append("  (select distinct doma_id ");
			sql.append("   from t_domain_dict ");
			sql.append("  where doma_status = 1 ");
			sql.append("  and doma_class = 1 ");
			sql.append("  start with doma_id = ? ");
			sql.append("   connect by prior doma_id = pdoma_id) ");
			sql.append(" ) ");
			sql.append(" ORDER BY ID");
			return this.query(sql.toString(), vo, vo.getDomaId());

		} else {
			sql.append(" ) ");
			sql.append(" ORDER BY ID");
			return this.query(sql.toString(), vo);
		}

	}

	/**
	 * 
	 * {删除扫描区域表时同时删除t_auto_topo_network t_auto_discover_network两张表的信息
	 * 
	 * 
	 * @return

	 */
	public int delNetWork(SecurityDomainVO vo) {
		String id = "";
		/*
		 * if("".equals(vo.getDelIds())){ id = vo.getDomaId(); }else{ id =
		 * vo.getDelIds().replaceAll(",", "','"); }
		 */
		id = vo.getDomaId().replaceAll(",", "','");
		String sql = " delete from t_auto_topo_network where scanzone_id in " + "('" + id + "')";
		String sql1 = " delete from t_auto_discover_network where scanzone_id in " + "('" + id + "')";
		String sql2 = " delete from t_agent_config  where scanzone_id in " + "('" + id + "')";
		this.update(sql);
		this.update(sql1);
		this.update(sql2);
		return 1;

	}

	/**
	 * 
	 * {修改代理服务器类型表信息}
	 * 
	 * @return

	 */
	public int updateAgentType() {
		StringBuilder sql = new StringBuilder();
		sql.append(" update t_soc_node n ");
		sql.append("  set n.agent_type = ");
		sql.append("(select ff.agent_type ");
		sql.append(" from (select wm_concat(ee.agent_type) agent_type, ee.node_id ");
		sql.append(" from (select CASE ");
		sql.append(" WHEN t1.agent_type = 1 THEN ");
		sql.append("  '日志收集模块' ");
		sql.append(" WHEN t1.agent_type = 2 THEN ");
		sql.append("'资产发现模块' ");
		sql.append(" ELSE ");
		sql.append(" '监控模块' ");
		sql.append(" END agent_type, ");
		sql.append(" t1.node_id ");
		sql.append(" from t_agent_config t1 ");
		sql.append(" group by t1.node_id, t1.agent_type ");
		sql.append(" union all ");
		sql.append("select '异常流量收集模块', c.AGENT_ID ");
		sql.append(" from t_ntars_config c ");
		sql.append("   group by c.agent_id) ee ");
		sql.append(" group by ee.node_id) ff ");
		sql.append(" where n.node_id = ff.node_id) ");
		return this.update(sql.toString());

	}

	/**
	 * 
	 * 向扫描区域添加域名称，将域名称已全路径的形式查询出来
	 * 
	 * @param domaId
	 * @return

	 */
	public String querySDNamePath(String pdomaId) {
		StringBuilder sql = new StringBuilder();
		String domaName = "";
		sql.append(" SELECT LTRIM(SYS_CONNECT_BY_PATH(t.doma_name, '>'), '>') name, t.doma_id ");
		sql.append(" FROM t_domain_dict t ");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND t.doma_id = '" + pdomaId + "'");
		sql.append(" START WITH t.doma_id in ( ");
		sql.append(" SELECT t1.doma_id  ");
		sql.append(" FROM t_domain_dict t1 ");
		sql.append(" WHERE t1.doma_class = 1 ");
		sql.append(" AND t1.doma_status = 1 ");
		sql.append(" AND t1.pdoma_id = '-1' ");
		sql.append("  ) ");
		sql.append(" CONNECT BY PRIOR t.doma_id = t.pdoma_id ");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			domaName = rowSet.getString("name");
		}
		return domaName;

	}

	/**
	 * 
	 * {查询单个安全域的层级}
	 * 
	 * @param domaId
	 * @return

	 */
	public String querySingleDomaLevel(String domaId) {
		StringBuilder sql = new StringBuilder();
		String level = "";
		String id;
		sql.append(" SELECT level,doma_id ");
		sql.append(" FROM T_DOMAIN_DICT b ");
		sql.append(" WHERE DOMA_STATUS = 1 ");
		sql.append("  AND DOMA_CLASS = 1 ");
		sql.append(" and doma_id = '" + domaId + "' ");
		sql.append("START WITH DOMA_ID = '-1' ");
		sql.append("CONNECT BY PRIOR DOMA_ID = PDOMA_ID ");
		SqlRowSet rowSet = this.query(sql.toString());
		while (rowSet.next()) {
			level = rowSet.getString("level");
			// id = rowSet.getString("doma_id");
		}
		return level;

	}

	/**
	 * 
	 * {获取安全域下一个权值编号}
	 * 
	 * @param vo
	 * @return

	 */
	public String queryNextSort(SecurityDomainVO vo) {
		String nextSort = "";
		StringBuilder sql = new StringBuilder();
		sql.append(" select nvl(max(to_number(t.doma_sort)),0) + 1 nextSort ");
		sql.append(" from t_domain_dict t ");
		sql.append(" where t.doma_status = 1 ");
		sql.append(" and t.doma_id not in ('SDUNKNOWN', 'BDUNKNOWN') ");
		sql.append(" and t.doma_class = ? and t.pdoma_id = ? ");
		SqlRowSet rs = this.query(sql.toString(), new Object[] { vo.getDomaClass(), vo.getPdomaId() });
		if (rs.next()) {
			nextSort = rs.getString("nextSort");
		} else {
			nextSort = "1";
		}

		return nextSort;
	}

	/**
	 * 
	 * {获取当前域的权重}
	 * 
	 * @param vo
	 * @return

	 */
	public String queryDomaSort(SecurityDomainVO vo) {
		String domaSort = "1";
		String sql = "select t.doma_sort from t_domain_dict t where t.doma_id = ?";
		SqlRowSet rs = this.query(sql, vo.getDomaId());
		if (rs.next()) {
			domaSort = rs.getString("doma_sort");
		}
		return domaSort;
	}

	/**
	 * 
	 * {获取需要互换位置的域的信息}
	 * 
	 * @param vo
	 * @return

	 */
	public Map<String, String> queryChangeDoma(SecurityDomainVO vo) {
		Map<String, String> map = new HashMap<String, String>();
		StringBuilder sql = new StringBuilder();
		sql.append(" select doma_sort, doma_id ");
		sql.append(" from (select t.doma_sort, t.doma_id ");
		sql.append(" from t_domain_dict t ");
		sql.append(" where t.doma_status = 1 and t.doma_id not in ('SDUNKNOWN', 'BDUNKNOWN') ");
		sql.append(" and t.doma_class = ? and t.pdoma_id = ? ");
		if ("UP".equals(vo.getMoveFlag())) {
			// 上移取值：倒序排列小于当前权重第一个值
			sql.append(" and t.doma_sort < ? ");
			sql.append(" order by t.doma_sort desc) ");
		} else {
			// 下移取值：正序排列大于当前权重第一个值
			sql.append(" and t.doma_sort > ? ");
			sql.append(" order by t.doma_sort) ");
		}
		sql.append(" where rownum = 1 ");
		Object[] obj = new Object[] { vo.getDomaClass(), vo.getPdomaId(), vo.getDomaSort() };
		SqlRowSet rs = this.query(sql.toString(), obj);
		if (rs.next()) {
			map.put("domaId", rs.getString("doma_id"));
			map.put("domaSort", rs.getString("doma_sort"));
			map.put("moveFlag", "true");
		} else {
			map.put("moveFlag", "false");
		}

		return map;
	}

	/**
	 * 
	 * {修改需要互换位置的域信息}
	 * 
	 * @param list
	 * @return

	 */
	public int updDomaSort(List<Map<String, String>> list) {
		int num = 0;
		String sql = "update t_domain_dict t set t.doma_sort = ? where t.doma_id = ?";
		for (Map<String, String> map : list) {
			int updNum = this.update(sql, new Object[] { map.get("domaSort"), map.get("domaId") });
			num += updNum;
		}
		return num;
	}

	/**
	 * 
	 * 查询子域下的Id信息
	 * 
	 * @param vo
	 * @return

	 */
	public String checkChildDomain(SecurityDomainVO vo) {
		String delIds = "";
		String id = vo.getDelIds().replaceAll(",", "','");
		StringBuilder sql = new StringBuilder(128);
		sql.append("select distinct  doma_id  from t_domain_dict where doma_status = 1 and doma_class = ? ");
		sql.append(" and doma_id <> '" + id + "' ");
		sql.append("start with doma_id in ('" + id + "') ");
		sql.append("connect by prior   doma_id =  pdoma_id");
		SqlRowSet rowSet = this.query(sql.toString(), vo.getDomaClass());
		while (rowSet.next()) {
			delIds = delIds + rowSet.getString("doma_id") + ",";
		}
		if (StringUtils.isNotEmpty(delIds)) {
			delIds = delIds.substring(0, (delIds.length() - 1));
		}

		return delIds;
	}

	/**
	 * 
	 * {根据域类型查询域的树结构}
	 * 
	 * @return

	 */
	public List<SecurityDomainVO> queryAllTreeByClass(String domaClass) {
		StringBuffer sql = new StringBuffer();
		sql.append(" with domt as ");
		sql.append(" ( ");
		sql.append(" select level domlevel, LTRIM(sys_connect_by_path(a.doma_id, ','), ',') treePath,a.doma_id,");
		sql.append(" a.doma_type,a.doma_class,a.doma_status,a.doma_abbreviation,a.doma_name,a.doma_level, ");
		sql.append(" a.OFFICE_TEL,a.EMAIL,a.pdoma_id,a.doma_address,a.doma_sort ");
		sql.append(" from t_domain_dict a ");
		sql.append(" where level > 1 ");
		sql.append(" start with a.doma_id = '-1'");
		sql.append(" CONNECT BY PRIOR a.DOMA_ID = a.PDOMA_ID ");
		sql.append(" order siblings by a.doma_sort ");
		sql.append(" ), ");
		sql.append(" VAGENT AS ( ");
		sql.append(" select C.DOMA_ID, ");
		sql.append("  WM_CONCAT(d.node_NAME) node_NAME, ");
		sql.append("  WM_CONCAT(d.node_id) node_id ");
		sql.append("  from T_SOC_NODE d, T_DOMAIN_VS_AGENT C ");
		sql.append("  WHERE C.NODE_ID = D.NODE_ID ");
		sql.append("  GROUP BY C.DOMA_ID ");
		sql.append(" ) ");
		sql.append(" select domt.treePath, domt.domlevel,domt.doma_name,domt.DOMA_ABBREVIATION, ");
		sql.append(
				" domt.OFFICE_TEL,domt.EMAIL,domt.pdoma_id,domt.doma_id,VAGENT.node_NAME,domt.doma_address,domt.doma_sort ");
		sql.append(" from domt  LEFT JOIN VAGENT ON domt.doma_id=VAGENT.doma_id ");
		sql.append(" where domt.DOMA_STATUS = 1 ");
		sql.append(" AND domt.DOMA_CLASS = ? order by domt.doma_sort ");
		SecurityDomainVO vo = new SecurityDomainVO();
		vo.setPageSize(100000);
		vo.setCurrentPageNum(1);
		return this.query(sql.toString(), vo, domaClass);
	}

	@Override
	public void updDomain4Drop(SecurityDomainVO tvo, int orderId, String point) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Map<String, String> map = new HashMap<String, String>();
		map.put("domaSort", orderId + "");
		map.put("domaId", tvo.getDomaId());
		list.add(map);
		updDomaSort(list);
	}
	
	
	public int checkSecurityIsExistsForDelete(String domaId) {
		String sql = "SELECT doma_id FROM t_domain_dict WHERE 1=1 and doma_status=1 and doma_id = ? ";
		SqlRowSet rs = this.query(sql, domaId);
		int result = 0;
		if (rs.next()) {
			rs.getString(1);
			result = 1;

		}
		return result;

	}
}
